﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using EHSubmit.Model;
using EHSubmit.Util;

namespace EHSubmit.Logic
{
    /// <summary>
    ///UserManager 的摘要说明
    ///相关方法：获取用户信息、用户信息被更新、管理员删除用户、管理员添加用户等
    /// </summary>
    public class UserManager
    {

        private const string SQL_SELECT_USER = "SELECT UPwd,UName,Sex,Birthday,Photo,Email,Phone,Institute,Department,Class,RoleId,CreatedTime,LoginCount,LastLoginID,LastLoginTime FROM [User] WHERE UID = @UID";
        private const string SQL_SELECT_CHECK_USER = "SELECT UID,UPwd,UName,Sex,Birthday,Photo,Email,Phone,Institute,Department,Class,RoleId,CreatedTime,LoginCount,LastLoginID,LastLoginTime FROM [User] WHERE UID = @UID AND UPwd = @UPwd";
        private const string SQL_UPDATE_USER = "UPDATE [User] SET UPwd = @UPwd,Sex = @Sex,Birthday = @Birthday,Photo = @Photo,Email = @Email,Phone = @Phone,Institute = @Institute,Department = @Department,Class = @Class,RoleId = @RoleId,CreatedTime = @CreatedTime,LoginCount = @LoginCount,LastLoginID = @LastLoginID,LastLoginTime = @LastLoginTime WHERE UID = @UID";
        private const string SQL_INSERT_USER = "INSERT INTO [User](UID,UPwd,UName,Sex,Birthday,Photo,Email,Phone,Institute,Department,Class,RoleId,CreatedTime,LoginCount,LastLoginID,LastLoginTime) VALUES(@UID,@UPwd,@UName,@Sex,@Birthday,@Photo,@Email,@Phone,@Institute,@Department,@Class,@RoleId,@CreatedTime,@LoginCount,@LastLoginID,@LastLoginTime)";
        private const string SQL_DELETE_USER = "DELETE FROM [User] WHERE UID= @UID";
        private const string SQL_USER_LOGIN = "UPDATE [User] SET LoginCount = LoginCount + 1, LastLoginTime = getdate(), LastLoginID=@LastLoginID WHERE UID=@UID";


        private const string PARM_UID = "@UID";                                 //User表的标识属性
        private const string PARM_UPWD = "@UPwd";                               //密码
        private const string PARM_UNAME = "@UName";                             //姓名
        private const string PARM_SEX = "@Sex";                                 //性别
        private const string PARM_BIRTHDAY = "@Birthday";                       //出生年月
        private const string PARM_PHOTO = "@Photo";                             //照片路径
        private const string PARM_EMAIL = "@Email";                             //电子邮件地址
        private const string PARM_PHONE = "@Phone";                             //电话
        private const string PARM_INSTITUTE = "@Institute";                     //所属学院
        private const string PARM_DEPARTMENT = "@Department";                   //所属专业
        private const string PARM_CLASS = "@Class";                             //所属班级号，如 0704*/
        private const string PARM_ROLEID = "@RoleId";                           //角色ID
        private const string PARM_CREATEDTIME = "@CreatedTime";                 //注册时间
        private const string PARM_LOGINCOUNT = "@LoginCount";                   //登陆次数
        private const string PARM_LASTLOGINID = "@LastLoginID";                 //最近登陆时的Id
        private const string PARM_LASTLOGINTIME = "@LastLoginTime";             //最近登陆时间


        /// <summary>
        /// 构造函数
        /// </summary>
        public UserManager(){}

        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <param name="id">用户ID</param>
        /// <returns>如果存在返回用户实体，否则返回空</returns>
        public User GetUser(string id)
        {
            if (id == null || id == string.Empty)
            {
                return null;
            }
            else
            {
                User user = null;

                SqlParameter parm = new SqlParameter(PARM_UID, SqlDbType.Char,16);
                parm.Value = id;
                using (SqlDataReader rdrUser = (SqlDataReader)SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_USER, parm))
                {
                    if (rdrUser.Read())
                    {
                        user = new User();
                        user.ID = id;
                        user.Pwd = rdrUser["UPwd"] is DBNull ? string.Empty : (string)rdrUser["UPwd"];                                         //密码
                        user.Name = rdrUser["UName"] is DBNull ? string.Empty : (string)rdrUser["UName"];                                      //姓名
                        user.Sex = rdrUser["Sex"] is DBNull ? string.Empty : (string)rdrUser["Sex"];                                           //性别
                        user.Birthday = rdrUser["Birthday"] is DBNull ? new DateTime(0) : (DateTime)rdrUser["Birthday"];                       //出生年月
                        user.Photo = rdrUser["Photo"] is DBNull ? string.Empty : (string)rdrUser["Photo"];                                     //照片路径
                        user.Email = rdrUser["Email"] is DBNull ? string.Empty : (string)rdrUser["Email"];                                     //电子邮件地址
                        user.Phone = rdrUser["Phone"] is DBNull ? string.Empty : (string)rdrUser["Phone"];                                     //电话
                        user.Institute = rdrUser["Institute"] is DBNull ? string.Empty : (string)rdrUser["Institute"];                         //所属学院
                        user.Department = rdrUser["Department"] is DBNull ? string.Empty : (string)rdrUser["Department"];                      //所属专业
                        user.Class = rdrUser["Class"] is DBNull ? string.Empty : (string)rdrUser["Class"];                                     //所属班级号，如 0704*/
                        user.RoleId = rdrUser["RoleId"] is DBNull ? -1 : (int)rdrUser["RoleId"];                                               //角色ID
                        user.Createdtime = rdrUser["Createdtime"] is DBNull ? DateTime.Now : (DateTime)rdrUser["Createdtime"];                 //注册时间
                        user.LoginCount = rdrUser["LoginCount"] is DBNull ? 0 : (int)rdrUser["LoginCount"];                                    //登陆次数
                        user.LastLoginId = rdrUser["Lastloginid"] is DBNull ? string.Empty : (string)rdrUser["Lastloginid"];                   //最近登陆时的Id
                        user.LastLoginTime = rdrUser["Lastlogintime"] is DBNull ? DateTime.Now : (DateTime)rdrUser["Lastlogintime"];           //最近登陆时间
                    }
                }
                return user;
            }
       }
        
        /// <summary>
        /// 验证用户
        /// </summary>
        /// <param name="id">用户ID</param>
        /// <param name="pwd">加密后的密码</param>
        /// <returns>如果验证成功返回用户信息，否则就返回空</returns>
        public User GetUser(string id, string pwd)
        {
            if (id == null || id == string.Empty)
            {
                return null;
            }

            User user = null;

            SqlParameter[] parms = new SqlParameter[2];
            parms[0] = new SqlParameter(PARM_UID, SqlDbType.NVarChar, 13);
            parms[1] = new SqlParameter(PARM_UPWD, SqlDbType.NVarChar, 16);

            parms[0].Value = id;
            parms[1].Value = pwd;
            
            
            using (SqlDataReader rdrUser = (SqlDataReader)SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_CHECK_USER, parms))
            {
                if (rdrUser.Read())     //如果存在，则返回的只有一条记录
                {
                    user = new User();
                    user.ID = rdrUser["UID"] is DBNull ? string.Empty : (string)rdrUser["UID"];
                    user.Pwd = rdrUser["UPwd"] is DBNull ? string.Empty : (string)rdrUser["UPwd"];                                                      //密码
                    user.Name = rdrUser["UName"] is DBNull ? string.Empty : (string)rdrUser["UName"];                                                   //姓名
                    user.Sex = rdrUser["Sex"] is DBNull ? string.Empty : (string)rdrUser["Sex"];                                                        //性别
                    user.Birthday = rdrUser["Birthday"] is DBNull ? DateTime.Now : (DateTime)rdrUser["Birthday"];                                       //出生年月
                    user.Photo = rdrUser["Photo"] is DBNull ? string.Empty : (string)rdrUser["Photo"];                                                  //照片路径
                    user.Email = rdrUser["Email"] is DBNull ? string.Empty : (string)rdrUser["Email"];                                                  //电子邮件地址
                    user.Phone = rdrUser["Phone"] is DBNull ? string.Empty : (string)rdrUser["Phone"];                                                  //电话
                    user.Institute = rdrUser["Institute"] is DBNull ? string.Empty : (string)rdrUser["Institute"];                                      //所属学院
                    user.Department = rdrUser["Department"] is DBNull ? string.Empty : (string)rdrUser["Department"];                                   //所属专业
                    user.Class = rdrUser["Class"] is DBNull ? string.Empty : (string)rdrUser["Class"];                                                  //所属班级号，如 0704*/
                    user.RoleId = rdrUser["RoleId"] is DBNull ? -1 : (int)rdrUser["RoleId"];                                                            //角色ID
                    user.Createdtime = rdrUser["Createdtime"] is DBNull ? DateTime.Now : (DateTime)rdrUser["Createdtime"];                              //注册时间
                    user.LoginCount = rdrUser["LoginCount"] is DBNull ? 0 : (int)rdrUser["LoginCount"];                                                 //登陆次数
                    user.LastLoginId = rdrUser["Lastloginid"] is DBNull ? string.Empty : (string)rdrUser["Lastloginid"];                                //最近登陆时的Id
                    user.LastLoginTime = rdrUser["Lastlogintime"] is DBNull ? DateTime.Now : (DateTime)rdrUser["Lastlogintime"];                        //最近登陆时间
                }
            }

            return user;
        }


        /*--------------------------------------------------------------------------------------------
          string tbName ,                                             -- 表名
          string fieldsToReturn = '*',                                -- 需要返回的列    如列1，列2，...
          string fieldToOrder ='',                                    -- 排序的字段名    如表1 (不用加[])  
          int pageSize = 10,                                          -- 页尺寸
          int pageIndex = 1,                                          -- 页码
          bool doCount = false;                                       --返回记录总数, 非 0 值则返回
          bool IsDesc = true,                                          -- 设置排序类型, 非 0 值则降序
          string criteria = ''                                        -- 查询条件 (注意: 不要加 where)
         --------------------------------------------------------------------------------------------*/
        /// <summary>
        /// 根据条件搜索用户获取分页的列表
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="searchCondit">按该条件搜索</param>
        /// <param name="conditField">条件的具体内容</param>
        /// <returns>单页的用户列表</returns>
        public IList<User> GetUsers(int pageIndex,string searchCondit,string conditField)
        {
            PagedDataProvider pagedDataProvider = new PagedDataProvider();
            pagedDataProvider.TbName = "User";
            pagedDataProvider.FieldsToReturn = "*";
            pagedDataProvider.FieldToOrder = "UName";
            pagedDataProvider.PageSize = 10;           //默认记录数为10
            pagedDataProvider.PageIndex = pageIndex;
            pagedDataProvider.DoCount = false;
            pagedDataProvider.IsDesc = false;
            pagedDataProvider.Criteria = searchCondit + " LIKE \'%" + conditField + "%\'";
            SqlDataReader rdrUsers = pagedDataProvider.GetResults();
            IList<User> userList = new List<User>();
            try
            {
                while (rdrUsers.Read())
                {
                    User user = new User();

                    user.ID = rdrUsers["UID"] is DBNull ? string.Empty : (string)rdrUsers["UID"];                                            //ID
                    user.Pwd = rdrUsers["UPwd"] is DBNull ? string.Empty : (string)rdrUsers["UPwd"];                                         //密码
                    user.Name = rdrUsers["UName"] is DBNull ? string.Empty : (string)rdrUsers["UName"];                                      //姓名
                    user.Sex = rdrUsers["Sex"] is DBNull ? string.Empty : (string)rdrUsers["Sex"];                                           //性别
                    user.Birthday = rdrUsers["Birthday"] is DBNull ? DateTime.Now : (DateTime)rdrUsers["Birthday"];                          //出生年月
                    user.Photo = rdrUsers["Photo"] is DBNull ? string.Empty : (string)rdrUsers["Photo"];                                     //照片路径
                    user.Email = rdrUsers["Email"] is DBNull ? string.Empty : (string)rdrUsers["Email"];                                     //电子邮件地址
                    user.Phone = rdrUsers["Phone"] is DBNull ? string.Empty : (string)rdrUsers["Phone"];                                     //电话
                    user.Institute = rdrUsers["Institute"] is DBNull ? string.Empty : (string)rdrUsers["Institute"];                         //所属学院
                    user.Department = rdrUsers["Department"] is DBNull ? string.Empty : (string)rdrUsers["Department"];                      //所属专业
                    user.Class = rdrUsers["Class"] is DBNull ? string.Empty : (string)rdrUsers["Class"];                                     //所属班级号，如 0704*/
                    user.RoleId = rdrUsers["RoleId"] is DBNull ? -1 : (int)rdrUsers["RoleId"];                                               //角色ID
                    user.Createdtime = rdrUsers["Createdtime"] is DBNull ? DateTime.Now : (DateTime)rdrUsers["Createdtime"];                 //注册时间
                    user.LoginCount = rdrUsers["LoginCount"] is DBNull ? 0 : (int)rdrUsers["LoginCount"];                                    //登陆次数
                    user.LastLoginId = rdrUsers["Lastloginid"] is DBNull ? string.Empty : (string)rdrUsers["Lastloginid"];                   //最近登陆时的Id
                    user.LastLoginTime = rdrUsers["Lastlogintime"] is DBNull ? DateTime.Now : (DateTime)rdrUsers["Lastlogintime"];           //最近登陆时间

                    userList.Add(user);
                }
            }
            finally
            {
                if (rdrUsers != null)   //关闭与数据库连接
                    rdrUsers.Close();
            }
            return userList;
        }

        /*--------------------------------------------------------------------------------------------
            string tbName ,                                             -- 表名
            bool doCount = false;                                       --返回记录总数, 非 0 值则返回
            string criteria = ''                                        -- 查询条件 (注意: 不要加 where)
          --------------------------------------------------------------------------------------------*/
        /// <summary>
        /// 根据条件搜索用户获取分页的列表的总页数
        /// </summary>
        /// <param name="searchCondit">按该条件搜索</param>
        /// <param name="conditField">条件的具体内容</param>
        /// <returns>列表的总页数</returns>
        public int GetUserPageCount(string searchCondit, string conditField)
        {
            PagedDataProvider pagedDataProvider = new PagedDataProvider();
            pagedDataProvider.TbName = "User";
            pagedDataProvider.DoCount = true;
            pagedDataProvider.Criteria = searchCondit + " LIKE \'%" + conditField + "%\'";
            pagedDataProvider.TotalCount = pagedDataProvider.GetRecordCount();
            if (pagedDataProvider.TotalCount == -1)
            {
                //有待处理解决
                return -1;
            }
            else
            {
                return pagedDataProvider.TotalCount;
            }
        }

        /// <summary>
        /// 管理员删除用户（单个）
        /// </summary>
        /// <param name="user">用户</param>
        public void DeleteUser(User user)
        {
            try
            {
                SqlParameter parm = new SqlParameter(PARM_UID, SqlDbType.NVarChar, 13);
                if (user.ID == null || user.ID == string.Empty)
                {
                    throw new Exception("userId 不能为空");
                }
                else
                {
                    parm.Value = user.ID;
                    int nResult = (int)SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_DELETE_USER, parm);
                }
            }
            catch (Exception ex)
            {
                LogHelper.Log(ex.Message, ex);
            }
        }

        /// <summary>
        /// 管理员删除用户（批量）
        /// </summary>
        /// <param name="users">用户ID</param>
        public void DeleteUser(User[] users)
        {
            for (int i = 0; i < users.Length; i++)
            {
                DeleteUser(users[i]);
            }
        }
        
        /// <summary>
        /// 用户信息被更新
        /// </summary>
        public void UpdateUser(User user)                    
        {
            try
            {
                if (user.ID == null || user.ID == string.Empty)
                {
                    throw new Exception("userId不能为空");
                }
                else
                {
                    int nresult;
                    SqlParameter[] parms = new SqlParameter[16];
                    parms[0] = new SqlParameter(PARM_UID, SqlDbType.NVarChar, 13);                     //User表的标识属性
                    parms[1] = new SqlParameter(PARM_UPWD, SqlDbType.NChar, 16);                       //密码
                    parms[2] = new SqlParameter(PARM_UNAME, SqlDbType.NChar, 5);                       //姓名
                    parms[3] = new SqlParameter(PARM_SEX, SqlDbType.NChar, 1);                         //性别
                    parms[4] = new SqlParameter(PARM_BIRTHDAY, SqlDbType.SmallDateTime);               //出生年月
                    parms[5] = new SqlParameter(PARM_PHOTO, SqlDbType.NVarChar, 100);                  //照片路径
                    parms[6] = new SqlParameter(PARM_EMAIL, SqlDbType.NVarChar, 30);                   //电子邮件地址
                    parms[7] = new SqlParameter(PARM_PHONE, SqlDbType.NVarChar, 11);                   //电话
                    parms[8] = new SqlParameter(PARM_INSTITUTE, SqlDbType.NChar, 10);                  //所属学院
                    parms[9] = new SqlParameter(PARM_DEPARTMENT, SqlDbType.NChar, 20);                 //所属专业
                    parms[10] = new SqlParameter(PARM_CLASS, SqlDbType.NChar, 4);                      //所属班级号，如 0704*/
                    parms[11] = new SqlParameter(PARM_CREATEDTIME, SqlDbType.DateTime);                //角色ID
                    parms[12] = new SqlParameter(PARM_ROLEID, SqlDbType.Int);                          //注册时间
                    parms[13] = new SqlParameter(PARM_LOGINCOUNT, SqlDbType.Int);                      //登陆次数
                    parms[14] = new SqlParameter(PARM_LASTLOGINID, SqlDbType.NChar, 15);               //最近登陆时的Id
                    parms[15] = new SqlParameter(PARM_LASTLOGINTIME, SqlDbType.DateTime);              //最近登陆时间
                    parms[0].Value = user.ID;                                                                           
                    parms[1].Value = user.Pwd;
                    parms[2].Value = user.Name;
                    parms[3].Value = user.Sex;
                    parms[4].Value = user.Birthday;
                    parms[5].Value = user.Photo == null ? "": user.Photo;
                    parms[6].Value = user.Email;
                    parms[7].Value = user.Phone;
                    parms[8].Value = user.Institute;
                    parms[9].Value = user.Department;
                    parms[10].Value = user.Class;
                    parms[11].Value = user.Createdtime;
                    parms[12].Value = user.RoleId;
                    parms[13].Value = user.LoginCount;
                    parms[14].Value = user.LastLoginId;
                    parms[15].Value = user.LastLoginTime;
                    nresult = (int)SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_UPDATE_USER, parms);
                }
            }
            catch (Exception ex)
            {
                LogHelper.Log(ex.Message, ex);
            }
        }


        /// <summary>
        /// 管理员添加用户（容错有待）
        /// </summary>
        public void AddUser(User user)
        {
            SqlParameter[] parms = new SqlParameter[16];
            parms[0] = new SqlParameter(PARM_UID, SqlDbType.NVarChar, 13);                   //User表的标识属性
            parms[1] = new SqlParameter(PARM_UPWD, SqlDbType.NChar, 16);                     //密码
            parms[2] = new SqlParameter(PARM_UNAME, SqlDbType.NChar, 5);                     //姓名
            parms[3] = new SqlParameter(PARM_SEX, SqlDbType.NChar, 1);                       //性别
            parms[4] = new SqlParameter(PARM_BIRTHDAY, SqlDbType.SmallDateTime);             //出生年月
            parms[5] = new SqlParameter(PARM_PHOTO, SqlDbType.NVarChar, 100);                //照片路径
            parms[6] = new SqlParameter(PARM_EMAIL, SqlDbType.NVarChar, 30);                 //电子邮件地址
            parms[7] = new SqlParameter(PARM_PHONE, SqlDbType.NVarChar, 11);                 //电话
            parms[8] = new SqlParameter(PARM_INSTITUTE, SqlDbType.NChar, 10);                //所属学院
            parms[9] = new SqlParameter(PARM_DEPARTMENT, SqlDbType.NChar, 20);               //所属专业
            parms[10] = new SqlParameter(PARM_CLASS, SqlDbType.NChar, 4);                    //所属班级号，如 0704*/
            parms[11] = new SqlParameter(PARM_CREATEDTIME, SqlDbType.DateTime);              //注册时间
            parms[12] = new SqlParameter(PARM_ROLEID, SqlDbType.Int);                        //角色ID
            parms[13] = new SqlParameter(PARM_LOGINCOUNT, SqlDbType.Int);                    //登陆次数
            parms[14] = new SqlParameter(PARM_LASTLOGINID, SqlDbType.NChar, 15);             //最近登陆时的Id
            parms[15] = new SqlParameter(PARM_LASTLOGINTIME, SqlDbType.DateTime);            //最近登陆时间
            parms[0].Value = user.ID;
            parms[1].Value = user.Pwd;
            parms[2].Value = user.Name;
            parms[3].Value = user.Sex;
            parms[4].Value = user.Birthday;
            parms[5].Value = user.Photo;
            parms[6].Value = user.Email;
            parms[7].Value = user.Phone;
            parms[8].Value = user.Institute;
            parms[9].Value = user.Department;
            parms[10].Value = user.Class;
            parms[11].Value = user.Createdtime;
            parms[12].Value = user.RoleId;
            parms[13].Value = user.LoginCount;
            parms[14].Value = user.LastLoginId;
            parms[15].Value = user.LastLoginTime;
            int nresult = (int)SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_INSERT_USER, parms);
        }


        public void AddOrUpdateUserFromRemote(User remote)
        {
            User localUser = null;
            localUser = this.GetUser(remote.ID);
            if (localUser != null)    //当地数据库已有已经经过验证的用户的信息
            {
                //由于不知道远程返回的有哪些数据，暂时认为只有密码可能发生更改
                localUser.Pwd = remote.Pwd;
                this.UpdateUser(localUser);
            }
            else                      //当地数据库没有已经经过验证的用户的信息
            {
                //由于不知道远程返回的有哪些数据，并对remote做了如何的具体赋值，暂时做如下处理
                this.AddUser(remote);
            }
        }

        public void DoLoginSuccessed(User u)
        {
            SqlParameter[] parms = new SqlParameter[2];
            parms[0] = new SqlParameter(PARM_UID, SqlDbType.NVarChar, 13);                   //User表的标识属性
            parms[1] = new SqlParameter(PARM_LASTLOGINID, SqlDbType.NChar, 15);             //最近登陆时的Id
            parms[0].Value = u.ID;
            parms[1].Value = u.LastLoginId;

            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_USER_LOGIN, parms);
        }
    }
}